package com.wxtf.platform.main.contraceptives;

import java.io.File;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.XLSUtils;
/**
 * <li>Title: TmaStoreCheckDetailAction.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.contraceptives</li>
 * <li>Description: 药具盘点明细表</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Jul 5, 2012 10:08:48 AM</li>
 *
 * @author liu_zhaojun
 * @version 1.0.0.0
 *
 */
public class TmaStoreCheckDetailAction extends LogicBase implements
		CommonsCallProvider {

	private static Log log = LogFactory.getLog(TmaStoreCheckDetailAction.class);
	
	public TmaStoreCheckDetailAction(String jsonArray) {

	}
	
	@Override
	public Object delete(Object jsonArray) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object insert(Object jsonArray) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object run() {
		Object msg = "";
		try {
			if ("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
			} else if ("detail".equals(this.getMethodName())) {
				msg = this.detail(this.getLogicData());
			} else if ("selectTmaDictionaryByTmadName".equals(this.getMethodName())) {
				msg = this.selectTmaDictionaryByTmadName(this.getLogicData());
			} else if("expData".equals(this.getMethodName())) {
				msg = this.expData(this.getLogicData());
			} else if("selectKuCun".equals(this.getMethodName())) {
				msg = this.selectKuCun(this.getLogicData());
			} else {
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}
			if(msg == null || "[null]".equals(msg)){
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
			//log.info(this.getClass().getName() +":"+this.getMethodName() + " -> " + msg);
		} catch (Exception e) {
			msg = "操作失败，请联系管理员";
			msg = ConstantInfoHelper.generateJSONString(ConstantInfoHelper.JSON_HEAD, ConstantInfoHelper.getFailResultDetail(msg.toString()), false);
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}
	
	/** 
	 * 描述：selectKuCun 查询库存
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	public Object selectKuCun(Object jsonArray) throws Exception {
		UserBean user = (UserBean)(this.getRequest()).getSession().getAttribute("userBean");
		String org_id = user.getCurOrgId();
		StringBuffer buff = new StringBuffer(" from tma_dictionary a inner join tm_general_dictionary b on a.tmad_kind=b.tmgd_id and a.reserve1='1' and a.tmad_status='0' and b.tmgd_type='105'");
		buff.append(" inner join kucun kc on a.tmad_id=kc.id and kc.orgid='").append(org_id).append("'")
		.append(" left join (select k.tmad_id as id,sum(k.tmaod_num) as num_s from tma_outcome_detail k inner join tma_outcome_main l on k.tmaom_id=l.tmaom_id and l.tmaom_type in ('2','3','4') and l.tmaom_is_valid='1' and l.org_id='").append(org_id).append("' group by k.tmad_id) kl on kl.id=a.tmad_id")
		.append(" group by a.tmad_id order by b.tmgd_name,a.tmad_name");
		String querySql = "select b.tmgd_name,a.tmad_name,a.tmad_company_name,a.tmad_batch_code,a.tmad_qgp,a.tmad_status,a.tmad_price,(case when sum(kc.num) is null then 0 else sum(kc.num) end)-(case when kl.num_s is null then 0 else kl.num_s end) as num" + buff.toString();
		String[] fields = {"tmgd_name","tmad_name","tmad_company_name","tmad_batch_code","tmad_qgp","tmad_status","tmad_price","num"};
		List<Map<String, String>> list = AccessDataBaseUtil.query(fields, querySql, null);
		DecimalFormat format = new DecimalFormat("0.00");
		for(int i=0;i<list.size();i++){
			Map<String,String> map = list.get(i);
			Double price = Double.valueOf(map.get("tmad_price"));
			Double num = Double.valueOf(map.get("num"));
			Double money = price * num;
			map.put("money", format.format(money));
			map.put("num", num.intValue()+"");
		}
		JSONArray array = JSONArray.fromObject(list);
		return "{\"Rows\":" + array.toString() + "}";
	}
	
	/** 
	 * 描述：select 查询盘点
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	@Override
	public Object select(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		UserBean user = (UserBean)(this.getRequest()).getSession().getAttribute("userBean");
		String org_id = user.getCurOrgId();
		String tmascm_id = jsonObject.getString("tmascm_id");
		String dis = jsonObject.getString("dis");
		StringBuffer buff = new StringBuffer("select ij.tmascm_id,ij.tmascd_id,b.tmgd_name,a.tmad_id,a.tmad_name,a.tmad_company_name,a.tmad_batch_code,a.tmad_qgp,a.tmad_status,a.tmad_price,(case when gh.back_tmascd_num is null then 0 else gh.back_tmascd_num end) as back_tmascd_num,(case when cd.tmaid_num is null then 0 else cd.tmaid_num end) as tmaid_num,(case when ef.tmaod_num is null then 0 else ef.tmaod_num end) as tmaod_num,(case when sum(kc.num) is null then 0 else sum(kc.num) end) as tmascd_num,(case when ij.tmascd_num_y is null then 0 else ij.tmascd_num_y end) as tmascd_num_y,(case when kl.num_s is null then 0 else kl.num_s end) as tmascd_num_s,(case when ij.tmascd_num_c is null then 0 else ij.tmascd_num_c end) as tmascd_num_c");
		buff.append(" from tma_dictionary a inner join tm_general_dictionary b on a.tmad_kind=b.tmgd_id")
		.append(" and a.reserve1='1' and a.tmad_status='0' and b.tmgd_type='105'");
		if(StringUtils.isNotBlank(dis)){
			buff.append(" inner join kucun kc on a.tmad_id=kc.id and kc.orgid='").append(org_id).append("'");
		}else{
			buff.append(" left join kucun kc on a.tmad_id=kc.id and kc.orgid='").append(org_id).append("'");
		}
		buff.append(" left join (select k.tmad_id as id,sum(k.tmaod_num) as num_s from tma_outcome_detail k inner join tma_outcome_main l on k.tmaom_id=l.tmaom_id and l.tmaom_type in ('2','3','4') and l.tmaom_is_valid='1' and l.org_id='").append(org_id).append("' group by k.tmad_id) kl on kl.id=a.tmad_id")
		.append(" left join (select i.tmascm_id,i.tmascd_id,i.tmad_id,i.tmascd_num_y,i.tmascd_num_c from tma_store_check_detail i inner join tma_store_check_main j on i.tmascm_id=j.tmascm_id and i.tmascm_id='").append(tmascm_id).append("' and j.org_id='").append(org_id).append("') ij on ij.tmad_id=a.tmad_id")		
		.append(" left join (select c.tmad_id,sum(c.tmaid_num) as tmaid_num from tma_income_detail c inner join tma_income_main d on c.tmaim_id=d.tmaim_id and d.tmaim_is_valid='1' and d.org_id='").append(org_id).append("' group by c.tmad_id) cd on a.tmad_id=cd.tmad_id")
		.append(" left join (select e.tmad_id,sum(e.tmaod_num) as tmaod_num from tma_outcome_detail e inner join tma_outcome_main f on e.tmaom_id=f.tmaom_id and f.tmaom_is_valid='1' and f.tmaom_type in ('0','1') and f.org_id='").append(org_id).append("' group by e.tmad_id) ef on a.tmad_id=ef.tmad_id")
		.append(" left join (select g.tmad_id,g.tmascd_num as back_tmascd_num from tma_store_check_detail g inner join tma_store_check_main h on g.tmascm_id=h.tmascm_id and h.tmascm_is_valid='1' and h.org_id='").append(org_id).append("' group by g.tmad_id order by h.tmascm_time desc) gh on a.tmad_id=gh.tmad_id")
		.append(" group by a.tmad_id order by a.tmad_kind desc,a.tmad_name");
		String[] fields = {"tmascm_id","tmascd_id","tmgd_name","tmad_id","tmad_name","tmad_company_name","tmad_batch_code","tmad_qgp","tmad_status","tmad_price","back_tmascd_num","tmaid_num","tmaod_num","tmascd_num","tmascd_num_yk","tmascd_num_s","tmascd_num_c"};
		List<Map<String, String>> list = AccessDataBaseUtil.query(fields, buff.toString(), null);
		List<Map<String, String>> l = new ArrayList<Map<String, String>>();
		if(list!=null&&list.size()>0){
			for(int i=0;i<list.size();i++){
				Map<String, String> map = list.get(i);
				map.put("tmascd_num", Double.valueOf(map.get("tmascd_num")).intValue()+"");
				l.add(map);
			}
		}		
		array = JSONArray.fromObject(l);
		return "{\"Rows\":" + array.toString() + "}";
	}
	
	/** 
	 * 描述：detail 明细
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	public Object detail(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		UserBean user = (UserBean)(this.getRequest()).getSession().getAttribute("userBean");
		String org_id = user.getCurOrgId();
		String tmascm_id = jsonObject.getString("tmascm_id");
		StringBuffer buff = new StringBuffer("select b.tmgd_name,a.tmad_name,a.tmad_company_name,a.tmad_batch_code,a.tmad_qgp,a.tmad_status,a.tmad_price,ij.back_tmascd_num,ij.tmaid_num,ij.tmaod_num,(ij.tmaid_num-ij.tmaod_num) as tmascd_num,ij.tmascd_num_y,ij.tmascd_num_s,ij.tmascd_num_c,ij.tmascd_num as num,ij.tmascd_num*a.tmad_price as money"); 
		buff.append(" from tma_dictionary a inner join tm_general_dictionary b on a.tmad_kind=b.tmgd_id and a.reserve1='1' and a.tmad_status='0' and b.tmgd_type='105'") 
		.append(" inner join (select i.tmad_id,i.back_tmascd_num,i.tmaid_num,i.tmaod_num,i.tmascd_num_y,i.tmascd_num_s,i.tmascd_num_c,i.tmascd_num from tma_store_check_detail i inner join tma_store_check_main j on i.tmascm_id=j.tmascm_id and i.tmascm_id='").append(tmascm_id).append("' and org_id='").append(org_id).append("') ij on ij.tmad_id=a.tmad_id") 
		.append(" group by a.tmad_id order by a.tmad_kind desc,a.tmad_name");
		String[] fields = {"tmgd_name","tmad_name","tmad_company_name","tmad_batch_code","tmad_qgp","tmad_status","tmad_price","back_tmascd_num","tmaid_num","tmaod_num","tmascd_num","tmascd_num_yk","tmascd_num_s","tmascd_num_c","num","money"};
		List<Map<String, String>> list = AccessDataBaseUtil.query(fields, buff.toString(), null);
		DecimalFormat format = new DecimalFormat("0.00");
		List<Map<String,String>> l = new ArrayList<Map<String,String>>();
		if(list!=null&&list.size()>0){
			for(int i=0;i<list.size();i++){
				Map<String,String> map = list.get(i);
				map.put("money", format.format(Double.valueOf(map.get("money"))));
				map.put("tmascd_num", Double.valueOf(map.get("tmascd_num")).intValue()+"");
				l.add(map);
			}
		}		
		array = JSONArray.fromObject(l);
		return "{\"Rows\":" + array.toString() + "}";
	}
	
	/** 
	 * 描述：selectTmaDictionaryByTmadName 出库入库明细
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Jul 10, 2012  5:12:42 PM
	 * @author liu_zhaojun
	 */
	public Object selectTmaDictionaryByTmadName(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tmad_name = jsonObject.getString("tmad_name");
		String org_id = jsonObject.getString("org_id");
		StringBuffer buff = new StringBuffer(" from (select a.tmad_name,a.tmad_company_name,cd.tmaid_num as num,t.caption as create_by,cd.create_time,'入库' as leixing,'入库' as t_type from tma_dictionary a inner join (select c.tmad_id,d.create_by,d.create_time,c.tmaid_num from tma_income_detail c inner join tma_income_main d on c.tmaim_id=d.tmaim_id and d.tmaim_is_valid='1' and org_id='").append(org_id).append("') cd on a.tmad_id=cd.tmad_id inner join t_user t on cd.create_by=t.user_id");
		buff.append(" where a.tmad_name='").append(tmad_name).append("'")
		.append(" union all ")
		.append("select a.tmad_name,a.tmad_company_name,ef.tmaod_num as num,t.caption as create_by,ef.create_time,'出库' as leixing,(case when ef.tmaom_type='2' then '使用报废' when ef.tmaom_type='3' then '库存报废' when ef.tmaom_type='4' then '过期报废' else '出库' end)")
		.append(" from tma_dictionary a inner join (select e.tmad_id,f.create_by,f.create_time,f.tmaom_type,e.tmaod_num from tma_outcome_detail e inner join tma_outcome_main f on e.tmaom_id=f.tmaom_id and f.tmaom_is_valid='1' and f.org_id='").append(org_id).append("') ef on a.tmad_id=ef.tmad_id inner join t_user t on ef.create_by=t.user_id")
		.append(" where a.tmad_name='").append(tmad_name).append("'")
		.append(" union all ")
		.append("select a.tmad_name,a.tmad_company_name,ef.tmascd_num_y as num,t.caption as create_by,ef.create_time,'出库' as leixing,'盘盈亏' as t_type")
		.append(" from tma_dictionary a inner join (select e.tmad_id,e.tmascd_num_y,f.create_by,f.create_time from tma_store_check_detail e inner join tma_store_check_main f on e.tmascm_id=f.tmascm_id and f.tmascm_is_valid='1' and f.org_id='").append(org_id).append("') ef on a.tmad_id=ef.tmad_id inner join t_user t on ef.create_by=t.user_id")
		.append(" where a.tmad_name='").append(tmad_name).append("'")
		.append(" union all ")
		.append("select a.tmad_name,a.tmad_company_name,ef.tmascd_num_c as num,t.caption as create_by,ef.create_time,'出库' as leixing,'宣传' as t_type")
		.append(" from tma_dictionary a inner join (select e.tmad_id,e.tmascd_num_c,f.create_by,f.create_time from tma_store_check_detail e inner join tma_store_check_main f on e.tmascm_id=f.tmascm_id and f.tmascm_is_valid='1' and f.org_id='").append(org_id).append("') ef on a.tmad_id=ef.tmad_id inner join t_user t on ef.create_by=t.user_id")
		.append(" where a.tmad_name='").append(tmad_name).append("'")
		.append(") u");
		String countSql = "select count(*)" + buff.toString();
		String querySql = "select u.tmad_name,u.tmad_company_name,u.num,u.create_by,u.create_time,u.leixing,u.t_type" + buff.toString();
		String[] fields = {"tmad_name","tmad_company_name","num","create_by","create_time","leixing","t_type"};
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
	
	/** 
	 * 描述：expData 人员导出
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Sep 5, 2012  5:06:00 PM
	 * @author liu_zhaojun
	 */
	public Object expData(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray); 
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tmascm_id = jsonObject.getString("tmascm_id");
		UserBean user = (UserBean)(this.getRequest()).getSession().getAttribute("userBean");
		String org_id = user.getCurOrgId();
		StringBuffer buff = new StringBuffer("select b.tmgd_name,a.tmad_name,a.tmad_company_name,a.tmad_batch_code,a.tmad_qgp,a.tmad_spec,s.tmascd_num,a.tmad_price,(s.tmascd_num*a.tmad_price) as money from TMA_DICTIONARY a inner join tm_general_dictionary b on a.tmad_kind=b.tmgd_id and b.tmgd_type='105' inner join (select i.tmascm_id,i.tmad_id,i.tmascd_num from tma_store_check_detail i inner join tma_store_check_main j on i.tmascm_id=j.tmascm_id");
		buff.append(" and org_id='").append(org_id).append("'")
		.append(") s  on a.tmad_id=s.tmad_id and s.tmascm_id='")
		.append(tmascm_id)
		.append("' order by a.tmad_kind desc,a.tmad_name");		
		List<Object[]> list = AccessDataBaseUtil.query(buff.toString(), null);		
		String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
		String model = url + "/model/tmaStoreCheck.xls";
		String file = url + "/file/tmaStoreCheck.xls";
		String rfile = "jsp/app/xls/file/tmaStoreCheck.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);
		
		if(list.size() > 0){
			XLSUtils.writeXls(list, fto, "盘点一览表", 1);//写xls内容
		} else {
			return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"导出结果集没有记录\"}";
		}
		
		return "\"" + rfile + "\"";
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		// TODO Auto-generated method stub
		return null;
	}

}
